package com.LGF.Book.dao;

import com.LGF.Book.bean.Member;
import com.LGF.Book.util.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class MemberDao {
    QueryRunner runner = new QueryRunner();

    public int mem_modify(long memId,String mname,String sex,String tel,String idNumber) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="update member set mname=?,sex=?,tel=?,idNumber=? where id=?";
        int count = runner.update(conn, sql, mname,  sex, tel, idNumber, memId);
        DBHelper.close(conn);
        return count;

    }

    /**
     * 根据id，密码和权限获取会员对象
     * @param id
     * @param pwd
     * @param userId
     * @return
     * @throws SQLException
     */
    public Member getMember(long id,String pwd,long userId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from member where id=? and pwd=?and userid=?";

        Member member = runner.query(conn, sql, new BeanHandler<Member>(Member.class), id, pwd, userId);
        DBHelper.close(conn);
        return member;
    }

    /**
     * 根据姓名，密码，身份证号查询会员信息
     * @param mname
     * @param pwd
     * @param idNumber
     * @return
     * @throws SQLException
     */
    public Member getMember(String mname,String pwd,String idNumber) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from member where mname=? and pwd=?and idNumber=?";
        Member member = runner.query(conn, sql, new BeanHandler<Member>(Member.class), mname, pwd, idNumber);
        DBHelper.close(conn);
        return member;

    }

    /**
     * 修改会员密码
     * @param id
     * @param pwd
     * @return
     * @throws SQLException
     */
    public int modify(long id ,String pwd) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="update member set pwd=? where id =?";
        int count = runner.update(conn, sql, pwd, id);
        DBHelper.close(conn);
        return count;
    }


    /**
     * 添加会员信息 注册功能
     * @param mname
     * @param pwd
     * @param sex
     * @param typeId
     * @param balance
     * @param tel
     * @param idNumber
     * @return
     * @throws SQLException
     */
    public int register(String mname,String pwd,String sex,long typeId,double balance,String tel,String idNumber) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="insert into member(mname,pwd,sex,typeid,userid,balance,regdate,tel,idNumber) values(?,?,?,?,2,?,CURRENT_DATE,?,?)";
        int count = runner.update(conn, sql,  mname, pwd, sex, typeId, balance, tel, idNumber);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 添加会员
     * @param id
     * @param mname
     * @param pwd
     * @param sex
     * @param typeId
     * @param balance
     * @param tel
     * @param idNumber
     * @return
     * @throws SQLException
     */
    public int add(long id ,String mname,String pwd,String sex,long typeId,double balance,String tel,String idNumber) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="insert into member(id,mname,pwd,sex,typeid,userid,balance,regdate,tel,idNumber) values(?,?,?,?,?,2,?,CURRENT_DATE,?,?)";
        int count = runner.update(conn, sql, id, mname, pwd, sex, typeId, balance, tel, idNumber);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 查询所有会员用户
     * @return
     * @throws SQLException
     */
    public List<Member> getAll() throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select id,mname,pwd,sex,typeId,userId,balance,regdate,tel,idNumber from member where userId=2";
        List<Member> members = runner.query(conn, sql, new BeanListHandler<Member>(Member.class));
        DBHelper.close(conn);
        return members;
    }

    /**
     * 根据ID查询会员信息
     * @param id
     * @return
     * @throws SQLException
     */
    public Member getById(long id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from member where id=?";
        Member member = runner.query(conn, sql, new BeanHandler<Member>(Member.class), id);
        DBHelper.close(conn);
        return member;
    }

    /**
     * 分页功能
     * @param page
     * @param limit
     * @return
     * @throws SQLException
     */
    public List<Member>  getByPage(int page,int limit) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="select * from member  where userId =2 limit ?,? ";
        List<Member> members = runner.query(conn, sql, new BeanListHandler<Member>(Member.class),page,limit);
        DBHelper.close(conn);
        return members;
    }

    /**
     * 获取会员的人数
     * @return
     * @throws SQLException
     */
    public int getCount() throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="select count(id) from member where userId=2";
        //返回Number类型
        Number data = runner.query(conn, sql, new ScalarHandler<>());

        int count = data.intValue();

        return count;
    }

    /**
     * 修改会员信息
     * @param id
     * @param mname
     * @param pwd
     * @param sex
     * @param typeId
     * @param balance
     * @param tel
     * @param idNumber
     * @return
     * @throws SQLException
     */
    public int mem_modify(long id,String mname,String pwd,String sex,long typeId,double balance,String tel,String idNumber) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="update member set mname=?,pwd=?,sex=?,typeId=?,balance=?,tel=?,idNumber=? where id=?";
        int count = runner.update(conn, sql, mname, pwd, sex, typeId, balance, tel, idNumber, id);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 根据ID删除会员信息
     * @param id
     * @return
     * @throws SQLException
     */
    public int mem_remove(long id) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql ="delete from member where id=?";
        int count = runner.update(conn, sql, id);
        DBHelper.close(conn);
        return count;
    }

    /**
     * 根据id模糊查询会员信息
     * @param memId
     * @return
     */
    public List<Member> selectById(String memId) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="select * from member  where id like '%' ? '%'";
        List<Member> members = runner.query(conn, sql, new BeanListHandler<Member>(Member.class), memId);
        DBHelper.close(conn);
        return members;
    }
    public List<Member> selectByMname(String mname) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="select * from member  where mname like '%' ? '%'";
        List<Member> members = runner.query(conn, sql, new BeanListHandler<Member>(Member.class), mname);
        DBHelper.close(conn);
        return members;
    }

    /**
     * 根据会员id修改卡中的金额
     * @param memId
     * @param balance >0：归还  <0：借书-押金
     * @return
     * @throws SQLException
     */
    public int modifyBalance( long memId,double balance) throws SQLException {
        Connection conn = DBHelper.getConnection();
        String sql="update member set balance = balance+? where id=?";
        int count = runner.update(conn, sql, balance, memId);
        DBHelper.close(conn);
        return count;
    }


}
